<?php 
session_start();
if(isset($_GET['genexcel'])){
    header("Content-type: application/vnd.ms-excel; name='excel'");
    header("Content-Disposition: filename=Facturas.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
}
define('DB_CHARSET', 'utf8');
include('../../../include/db.inc.php');
include('../../../cfg/cfg.inc.php');
require_once('Conection_DataBase.php');

$db=new DbManager();
$db->connect(DBHOST, DBUSER, DBPWD,true,true,DBNAME,"");

function getTasaProm($db,$where) {
    $where=$where!=""?" Where TASA_CAMBIO>0 AND ".$where:" Where TASA_CAMBIO>0";
    $res=$db->query_toArray("select AVG(TASA_CAMBIO) as promedio from cllg_factura $where");
    $res=$res[0];
    return $res["promedio"];
}
function getTotalByColumn($db,$col,$where) {
    $where=$where!=""?" Where ".$where:$where;
    $res=$db->query_toArray("select sum($col) as total from cllg_factura $where");
    $res=$res[0];
    return $res["total"];
}

mysql_select_db($database_jssue, $jssue);

$todasFechas = isset($_GET["checkAllFechas"]) ? intval($_GET["checkAllFechas"]) : 0;
$porAlumno = isset($_GET["usaEstudiante"]) ? intval($_GET["usaEstudiante"]) : 0;
$porTipoPago = isset($_GET["usaTipoPago"]) ? intval($_GET["usaTipoPago"]) : 0;
$idEstudiante = isset($_GET["idEstudiante"]) ? $_GET["idEstudiante"] : '';
$tipoPago = $_GET["tipoPago"];
$desde = $_GET["txtFechDesde"];
$hasta = $_GET["txtFechHasta"];
$where="";
if ($todasFechas==1) {
    if ($porAlumno==1) {
        $where="cllg_factura.FK_STUDENT = '$idEstudiante'";
        $queryFacturas = "select * from cllg_factura Where $where";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
    if ($porTipoPago==1) {
        $where="NOT ISNULL(cllg_factura.$tipoPago) and cllg_factura.$tipoPago>0";
        $queryFacturas = "select * from cllg_factura Where $where";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
    if ($porAlumno==1 and $porTipoPago==1) {
        $where="(NOT ISNULL(cllg_factura.$tipoPago) and cllg_factura.$tipoPago>0) AND cllg_factura.FK_STUDENT = '$idEstudiante'";
        $queryFacturas = "select * from cllg_factura where $where";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
    if (!$porAlumno==1 and !$porTipoPago==1) {
        $queryFacturas = "select * from cllg_factura";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
}
else {
    if ($porAlumno==1) {
        $where="cllg_factura.FK_STUDENT = '$idEstudiante' and cllg_factura.FECHA BETWEEN '$desde' and '$hasta'";
        $queryFacturas = "select * from cllg_factura where $where";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
    if ($porTipoPago==1) {
        $whereParche="cllg_factura.TIPO_PAGO like '%$tipoPago'";
        $where="((NOT ISNULL(cllg_factura.$tipoPago) and cllg_factura.$tipoPago>0) OR $whereParche)
            and cllg_factura.FECHA BETWEEN '$desde' and '$hasta'";
        $queryFacturas = "select * from cllg_factura where $where";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
    if (!$porAlumno==1 and !$porTipoPago==1) {
        $where="cllg_factura.FECHA BETWEEN '$desde' and '$hasta'";
        $queryFacturas = "select * from cllg_factura where $where";
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
    if ($porAlumno==1 and $porTipoPago==1) {
        $where="cllg_factura.FECHA BETWEEN '$desde' and '$hasta' AND (NOT ISNULL(cllg_factura.$tipoPago) and cllg_factura.$tipoPago>0) AND cllg_factura.FK_STUDENT = '$idEstudiante'";
        $queryFacturas = "select * from cllg_factura where $where";
        //echo $queryFacturas;
        $rsFacturas = mysql_query($queryFacturas, $jssue) or die(mysql_error());
    }
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Facturas Emitidas</title>
    
        <link href="css/print.css" rel="stylesheet" type="text/css" />
        <style type="text/css">
            <!--
            .Estilo1 {
                color: #FF0000;
                font-size: 9px;
            }
            -->
        </style>
        <link rel="stylesheet" href="../../../bootstrap/css/bootstrap.css" />
        <script src="../../../js/prototype162.js"></script>
    </head>

    <body>
        <form id="form1" name="form1" method="get" action="RepFacturaDetalle.php">
            <div id="wrap" style="width:100%">
                <table width="100%">
                    <tr>
                        <th>
                            <legend>Facturas Emitidas Detalladas
                            <?PHP if(!$todasFechas==1) {?>
                            desde
                            <?=date("d-m-Y",strtotime($desde))?>
                            al <?=date("d-m-Y",strtotime($hasta))?>
                            <?php } ?>
                            </legend>
                        </th>
                        <th><legend>
                            <div id="controles" class="btn-group">
                                <input type="submit" class="btn btn-info" name="excel" id="excel" value="Excel" />
                                <input name="imprimir" class="btn btn-info" type="button" onclick="$('controles').hide();window.print();alert('Impresion Lista!');$('controles').show();" value="Imprimir" />
                            </div></legend>
                            <input name="genexcel" type="hidden" id="genexcel" value="1" />
                            <input name="checkAllFechas" type="hidden" id="checkAllFechas" value="<?=$todasFechas?>" />
                            <input name="txtFechDesde" type="hidden" id="txtFechDesde" value="<?=$desde?>" />
                            <input name="txtFechHasta" type="hidden" id="txtFechHasta" value="<?=$hasta?>" />
                        </th>
                    </tr>
                </table>
                <table width="100%" border="0" cellspacing="0" cellpadding="0" style="font-size:10px" class="table table-striped table-bordered">
                    <tr style="font-size:10px">
                        <th width="8%" valign="top">Factura</th>
                        <th width="6%" valign="top">Fecha</th>
                        <th width="20%" valign="top">Nombre</th>
                        <th width="20%" valign="top">Concepto</th>
                        <th width="4%" valign="top">Efectivo Quetzales</th>
                        <th width="4%" valign="top">Cheques Bco. BI</th>
                        <th width="4%" valign="top">Cheques Otros Bancos</th>
                        <th width="4%" valign="top">Deposito Directo</th>
                        <th width="4%" valign="top">Dolares</th>
                        <th width="2%" valign="top">No. Boleta </th>
                        <th width="2%" valign="top">No. Cheque </th>
                        <th width="4%" valign="top" >Nombre Banco</th>
                        <th width="4%" valign="top">Total Factura Q</th>
                        <th width="6%" valign="top">TIPO CAMBIO</th>
                    </tr>
                    <?PHP
                    $varSumEfect=0;$varSumBI=0;$varSumOTRO=0;$varSumDepo=0;$varSumDolares=0;
                    $varSumTotal=0;
                    while ($factura=mysql_fetch_assoc($rsFacturas)){
                        $idFactura = $factura['ID'];
                        $TipoPago = $factura['TIPO_PAGO'];
                        $stadoFactu = $factura['STATUS'];
                        $varEfect = "";
                        $varDepo = "";
                        $varBI = "";
                        $varOTRO = "";
                        $totFac=$factura['TOTAL'];
                        $nocheque="";
                        $noboleta="";
                        $banco="";

                        if($stadoFactu==1){

                            $varEfect=$factura["EFECTIVO"];
                            $varEfect+=$factura["TARJETA"];
                            $varDepo=$factura["DEPOSITO"];
                            $varSumEfect +=$factura["DOLARES"]==0?$varEfect:0;
                            $varSumDolares+=$factura["DOLARES"];
                            $noboleta=$factura['NO_BOLETA'];
                            if(doubleval($varDepo)>0&&$factura["DOLARES"]==0){
                                $varSumDepo += $varDepo;
                            }
                            $tCheque=$factura["CHEQUE"];
                            if($tCheque>0&&$factura["DOLARES"]==0){
                                $queryChequesFactura = "select * from cllg_cheque where FK_FACURA = '$idFactura'";
                                $rstChequesFactura = mysql_query($queryChequesFactura, $jssue) or die(mysql_error());
                                $rowChequeFactura=mysql_fetch_assoc($rstChequesFactura);
                                $bancoCheque = $rowChequeFactura['BANCO'];
                                $nocheque=$rowChequeFactura['NUMERO'];
                                if($bancoCheque=="BI"){
                                    $varBI +=$tCheque;
                                    $varSumBI += $varBI;
                                }else{
                                    $varOTRO +=$tCheque;
                                    $varSumOTRO +=$varOTRO;
                                    $banco=$rowChequeFactura["BANCO"];
                                }
                            }

                            //parche version anterior
                            if($TipoPago=="EFEC" || $TipoPago=="TAR"){
                                $varEfect = $totFac;
                                $varSumEfect = $varSumEfect + $varEfect;
                            }
                            if($TipoPago=="DEPO"){
                                $varDepo = $totFac;
                                $varSumDepo = $varSumDepo + $varDepo;
                                $noboleta=$factura['NO_BOLETA'];
                            }
                            if($TipoPago=="CHEQ"){
                                $queryChequesFactura = "select * from cllg_cheque where FK_FACURA = '$idFactura'";
                                $rstChequesFactura = mysql_query($queryChequesFactura, $jssue) or die(mysql_error());
                                $rowChequeFactura=mysql_fetch_assoc($rstChequesFactura);
                                $bancoCheque = $rowChequeFactura['BANCO'];
                                $nocheque=$rowChequeFactura['NUMERO'];
                                if($bancoCheque=="BI"){
                                    $varBI = $totFac;
                                    $varSumBI = $varSumBI + $varBI;
                                }else{
                                    $varOTRO = $totFac;
                                    $varSumOTRO = $varSumOTRO + $varOTRO;
                                    $banco=$rowChequeFactura["BANCO"];
                                }
                            }
                            //fin parche
                            $varSumTotal+=$factura["TOTAL"];
                        }
                        $queryDetalleFact = "select * from cllg_factura_detalle where cllg_factura_detalle.FK_FACTURA = '$idFactura' order by ID ASC";
                        $rstDetalleFact = mysql_query($queryDetalleFact, $jssue) or die(mysql_error());

                        ?>
                    <tr>
                        <td valign="top"> <?php if($stadoFactu==0){?><span class=""><?php } echo $factura['NUMERO'];?></span></td>
                        <td valign="top"> <?php if($stadoFactu==0){?><span class=""><?php } echo $factura['FECHA'];?></span></td>
                        <td valign="top"><?= $stadoFactu == 0 ? '<span class="anulada">ANULADA</span>' : ( $stadoFactu == 1 ? $factura['NOMBRE'] : '<span class="anulada">EN TRAMITE</span>' ); ?></td>
                        <td valign="top" style="font-size:7px;">
                            <?PHP while($rowDetalleFact=mysql_fetch_assoc($rstDetalleFact)){
                                if($stadoFactu==0){?>
                            <span class="Estilo1">
                                <?PHP echo $rowDetalleFact['DESCRIPCION'];?>
                            </span>
                            <?php }else{  echo $rowDetalleFact['DESCRIPCION']; } echo ","; }?>
                        </td>
                        <td align="right" valign="top"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo ($factura["DOLARES"]==0&&$varEfect>0)?number_format($varEfect,2):"";?></span></td>
                        <td align="right" valign="top"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo ($factura["DOLARES"]==0&&$varBI>0)?number_format($varBI,2):"";?></span></td>
                        <td align="right" valign="top"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo ($factura["DOLARES"]==0&&$varOTRO>0)?number_format($varOTRO,2):"";?></span></td>
                        <td align="right" valign="top"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo ($factura["DOLARES"]==0&&$varDepo>0)?number_format($varDepo,2):"";?></span></td>
                        <td align="right" valign="top"><?=($stadoFactu!=0 & $stadoFactu!=2 && $factura["DOLARES"]>0)?number_format($factura["DOLARES"],2):""?></td>
                        <td align="right" valign="top" style="font-size:9px;"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo $noboleta;?></span></td>
                        <td align="right" valign="top" style="font-size:9px;"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo $nocheque;?></span></td>
                        <td align="right" valign="top"><?php if($stadoFactu==0){?><span class="Estilo1"><?php } echo $banco;?></span></td>
                        <td align="right" valign="top"><?=($stadoFactu!=0 & $stadoFactu!=2)?number_format($factura["TOTAL"],2):""?></td>
                        <td align="right" valign="top"><?=($stadoFactu!=0 & $stadoFactu!=2 && $factura["TASA_CAMBIO"]>0)?number_format($factura["TASA_CAMBIO"],2):""?></td>
                    </tr>

        <?PHP }?>
                    <tr style="font-weight:bolder">
                        <td valign="top">&nbsp;</td>
                        <td valign="top">&nbsp;</td>
                        <td valign="top">&nbsp;</td>
                        <td valign="top" class="">Totales</td>
                        <td align="right" valign="top" class="">
                            <?=number_format($varSumEfect,2)?>
                        </td>
                        <td align="right" valign="top" class="">
                            <?=number_format($varSumBI,2)?>
                        </td>
                        <td align="right" valign="top" class="">
                            <?=number_format($varSumOTRO,2)?>
                        </td>
                        <td align="right" valign="top" class="">
                            <?=number_format($varSumDepo,2)?>
                        </td>
                        <td align="right" valign="top" class="">
                            <?=number_format($varSumDolares,2)?>
                        </td>
                        <td align="right" valign="top" class="" style="font-size:5px;">&nbsp;</td>
                        <td align="right" valign="top" class="" style="font-size:5px;">&nbsp;</td>
                        <td align="right" valign="top" class="">&nbsp;</td>
                        <td align="right" valign="top" class="">
                            <?=number_format($varSumTotal,2)?>
                        </td>
                        <td align="right" valign="top" class="">&nbsp;</td>
                    </tr>
                </table>
            </div>
            <br />
            <div id="resumendiv">
                <table width="100%" border="0" id="resumen" cellspacing="0" cellpadding="0">
                    <tr>
                        <td width="50%"><table width="100%" border="0" cellspacing="0">
                                <tr>
                                    <td colspan="2"><legend>RESUMEN GENERAL</legend></td>   
                                </tr>
                                <tr>
                                    <td>Total Efectivo</td>
                                    <td align="right" class="b"><?=number_format($varSumEfect,2)?></td>
                                </tr>
                                <tr>
                                    <td width="55%">Total Cheques BI</td>
                                    <td width="45%" align="right" class="b"><?=number_format($varSumBI,2)?></td>
                                </tr>
                                <tr>
                                    <td>Total Cheques Otros Bancos</td>
                                    <td align="right" class="b"><?=number_format($varSumOTRO,2)?></td>
                                </tr>
                                <tr>
                                    <td>Total Depositos Directos</td>
                                    <td align="right" class="b"><?=number_format($varSumDepo,2)?></td>
                                </tr>
                                <tr class="b"">
                                    <td>Total Ingresos Quetzales</td>
                                    <td class="b" align="right">Q.
                                        <?PHP $ingresoQ=$varSumEfect+
                                        +$varSumBI+$varSumOTRO+$varSumDepo; ?>
                                        <?=number_format($ingresoQ,2)?></td>
                                </tr>
                                <tr>
                                    <td class="b">Total Ingresos Dolares</td>
                                    <td align="right" class="b"><?=number_format($varSumDolares,2)?></td>
                                </tr>
                                <tr>
                                    <td width="55%">Dolares por Tipo de Cambio</td>
                                    <td align="right" class="b"><?php $ingresoDol=getTasaProm($db, $where)*$varSumDolares; ?>
                                    <?=number_format($ingresoDol,2)?></td>
                                </tr>
                                <tr class="b">
                                    <td class="b">Total Ingresos</td>
                                    <td class="b" align="right" ><?=number_format($ingresoDol+$ingresoQ,2)?></td>
                                </tr>
                                <tr class="b">
                                    <td >Total Facturado</td>
                                    <td align="right" class="b"><?=number_format($varSumTotal,2)?></td>
                                </tr>
                                <tr>
                                    <td >Diferencia</td>
                                    <td align="right" class="b" ><?=number_format($varSumTotal-($ingresoDol+$ingresoQ),2)?></td>
                                </tr>
                                <tr>
                                    <td >No. Reporte </td>
                                    <td align="right" class="b"><?=$_GET['noreporte']?></td>
                                </tr>
                                <tr>
                                    <td >No. Boleta Deposito</td>
                                    <td align="right" class="b"><?=$_GET['noboleta']?></td>
                                </tr>
                        </table></td>
                        <td width="50%" valign="top">
                            <div style="margin-left:30%" id="firmas">
                                <table width="300" border="0" cellspacing="0" cellpadding="0">
                                    <tr>
                                        <td width="91">&nbsp;</td>
                                        <td width="177">&nbsp;</td>
                                        <td width="32">&nbsp;</td>
                                    </tr>
                                    <tr>
                                        <td>Elaborado Por</td>
                                        <td class="subrayado">&nbsp;</td>
                                    </tr>
                                    <tr>
                                        <td>&nbsp;</td>
                                        <td>&nbsp;</td>
                                        <td>&nbsp;</td>
                                    </tr>
                                    <tr>
                                        <td>Revisado Por</td>
                                        <td class="subrayado">&nbsp;</td>
                                    </tr>
                                </table>
                            </div>
                        </td>
                    </tr>
                </table>
            </div>

            </form>
    </body>
</html>